{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>variety</th>\n",
       "      <th>永安期货</th>\n",
       "      <th>海通期货</th>\n",
       "      <th>中信期货</th>\n",
       "      <th>合计</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20201019</td>\n",
       "      <td>A</td>\n",
       "      <td>-5856.0</td>\n",
       "      <td>2150.0</td>\n",
       "      <td>3842.0</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20201019</td>\n",
       "      <td>AG</td>\n",
       "      <td>11654.0</td>\n",
       "      <td>17629.0</td>\n",
       "      <td>18061.0</td>\n",
       "      <td>47344.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20201019</td>\n",
       "      <td>AL</td>\n",
       "      <td>6131.0</td>\n",
       "      <td>-13768.0</td>\n",
       "      <td>-2093.0</td>\n",
       "      <td>-9730.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20201019</td>\n",
       "      <td>AP</td>\n",
       "      <td>-6673.0</td>\n",
       "      <td>501.0</td>\n",
       "      <td>824.0</td>\n",
       "      <td>-5348.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20201019</td>\n",
       "      <td>AU</td>\n",
       "      <td>5741.0</td>\n",
       "      <td>6397.0</td>\n",
       "      <td>11013.0</td>\n",
       "      <td>23151.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20201019</td>\n",
       "      <td>BU</td>\n",
       "      <td>-44227.0</td>\n",
       "      <td>7908.0</td>\n",
       "      <td>-19416.0</td>\n",
       "      <td>-55735.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20201019</td>\n",
       "      <td>C</td>\n",
       "      <td>84530.0</td>\n",
       "      <td>21981.0</td>\n",
       "      <td>-84945.0</td>\n",
       "      <td>21566.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20201019</td>\n",
       "      <td>CF</td>\n",
       "      <td>-8518.0</td>\n",
       "      <td>22319.0</td>\n",
       "      <td>-4996.0</td>\n",
       "      <td>8805.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20201019</td>\n",
       "      <td>CJ</td>\n",
       "      <td>-946.0</td>\n",
       "      <td>544.0</td>\n",
       "      <td>-345.0</td>\n",
       "      <td>-747.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20201019</td>\n",
       "      <td>CS</td>\n",
       "      <td>4406.0</td>\n",
       "      <td>4032.0</td>\n",
       "      <td>798.0</td>\n",
       "      <td>9236.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20201019</td>\n",
       "      <td>CU</td>\n",
       "      <td>-1048.0</td>\n",
       "      <td>6550.0</td>\n",
       "      <td>24612.0</td>\n",
       "      <td>30114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20201019</td>\n",
       "      <td>CY</td>\n",
       "      <td>113.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>-549.0</td>\n",
       "      <td>-376.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20201019</td>\n",
       "      <td>FG</td>\n",
       "      <td>15677.0</td>\n",
       "      <td>-13314.0</td>\n",
       "      <td>27076.0</td>\n",
       "      <td>29439.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20201019</td>\n",
       "      <td>FU</td>\n",
       "      <td>-49990.0</td>\n",
       "      <td>-8929.0</td>\n",
       "      <td>-39074.0</td>\n",
       "      <td>-97993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20201019</td>\n",
       "      <td>HC</td>\n",
       "      <td>24334.0</td>\n",
       "      <td>-9496.0</td>\n",
       "      <td>995.0</td>\n",
       "      <td>15833.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20201019</td>\n",
       "      <td>I</td>\n",
       "      <td>20035.0</td>\n",
       "      <td>-12118.0</td>\n",
       "      <td>-819.0</td>\n",
       "      <td>7098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20201019</td>\n",
       "      <td>IC</td>\n",
       "      <td>-3655.0</td>\n",
       "      <td>-2062.0</td>\n",
       "      <td>-13039.0</td>\n",
       "      <td>-18756.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20201019</td>\n",
       "      <td>IF</td>\n",
       "      <td>1089.0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>-7731.0</td>\n",
       "      <td>-4714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20201019</td>\n",
       "      <td>IH</td>\n",
       "      <td>917.0</td>\n",
       "      <td>1309.0</td>\n",
       "      <td>-5571.0</td>\n",
       "      <td>-3345.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20201019</td>\n",
       "      <td>J</td>\n",
       "      <td>3390.0</td>\n",
       "      <td>2440.0</td>\n",
       "      <td>-1929.0</td>\n",
       "      <td>3901.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20201019</td>\n",
       "      <td>JD</td>\n",
       "      <td>511.0</td>\n",
       "      <td>-997.0</td>\n",
       "      <td>-1258.0</td>\n",
       "      <td>-1744.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20201019</td>\n",
       "      <td>JR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>20201019</td>\n",
       "      <td>LR</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>20201019</td>\n",
       "      <td>LU</td>\n",
       "      <td>3948.0</td>\n",
       "      <td>-8418.0</td>\n",
       "      <td>-4554.0</td>\n",
       "      <td>-9024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>20201019</td>\n",
       "      <td>MA</td>\n",
       "      <td>-86053.0</td>\n",
       "      <td>-19818.0</td>\n",
       "      <td>-54694.0</td>\n",
       "      <td>-160565.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20201019</td>\n",
       "      <td>NI</td>\n",
       "      <td>4681.0</td>\n",
       "      <td>2594.0</td>\n",
       "      <td>-1709.0</td>\n",
       "      <td>5566.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>20201019</td>\n",
       "      <td>NR</td>\n",
       "      <td>-553.0</td>\n",
       "      <td>-11.0</td>\n",
       "      <td>-885.0</td>\n",
       "      <td>-1449.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>20201019</td>\n",
       "      <td>OI</td>\n",
       "      <td>11778.0</td>\n",
       "      <td>1690.0</td>\n",
       "      <td>4917.0</td>\n",
       "      <td>18385.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20201019</td>\n",
       "      <td>P</td>\n",
       "      <td>-5322.0</td>\n",
       "      <td>-10319.0</td>\n",
       "      <td>-4690.0</td>\n",
       "      <td>-20331.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>20201019</td>\n",
       "      <td>PB</td>\n",
       "      <td>-1690.0</td>\n",
       "      <td>-1704.0</td>\n",
       "      <td>1702.0</td>\n",
       "      <td>-1692.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>20201019</td>\n",
       "      <td>PF</td>\n",
       "      <td>26544.0</td>\n",
       "      <td>21555.0</td>\n",
       "      <td>-5999.0</td>\n",
       "      <td>42100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>20201019</td>\n",
       "      <td>PP</td>\n",
       "      <td>36860.0</td>\n",
       "      <td>9025.0</td>\n",
       "      <td>42997.0</td>\n",
       "      <td>88882.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>20201019</td>\n",
       "      <td>PTA</td>\n",
       "      <td>-55988.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-55285.0</td>\n",
       "      <td>-111273.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>20201019</td>\n",
       "      <td>RB</td>\n",
       "      <td>-1534.0</td>\n",
       "      <td>7284.0</td>\n",
       "      <td>44758.0</td>\n",
       "      <td>50508.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>20201019</td>\n",
       "      <td>RM</td>\n",
       "      <td>14735.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11146.0</td>\n",
       "      <td>25881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>20201019</td>\n",
       "      <td>RR</td>\n",
       "      <td>255.0</td>\n",
       "      <td>-372.0</td>\n",
       "      <td>-621.0</td>\n",
       "      <td>-738.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>20201019</td>\n",
       "      <td>RU</td>\n",
       "      <td>1575.0</td>\n",
       "      <td>-6495.0</td>\n",
       "      <td>-8145.0</td>\n",
       "      <td>-13065.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>20201019</td>\n",
       "      <td>SA</td>\n",
       "      <td>-417.0</td>\n",
       "      <td>-8409.0</td>\n",
       "      <td>-357.0</td>\n",
       "      <td>-9183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>20201019</td>\n",
       "      <td>SF</td>\n",
       "      <td>1178.0</td>\n",
       "      <td>2773.0</td>\n",
       "      <td>-1406.0</td>\n",
       "      <td>2545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>20201019</td>\n",
       "      <td>SM</td>\n",
       "      <td>-1080.0</td>\n",
       "      <td>3075.0</td>\n",
       "      <td>-14182.0</td>\n",
       "      <td>-12187.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>20201019</td>\n",
       "      <td>SN</td>\n",
       "      <td>-199.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>900.0</td>\n",
       "      <td>856.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>20201019</td>\n",
       "      <td>SP</td>\n",
       "      <td>-15316.0</td>\n",
       "      <td>5060.0</td>\n",
       "      <td>-9525.0</td>\n",
       "      <td>-19781.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>20201019</td>\n",
       "      <td>SR</td>\n",
       "      <td>-1533.0</td>\n",
       "      <td>16134.0</td>\n",
       "      <td>14261.0</td>\n",
       "      <td>28862.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>20201019</td>\n",
       "      <td>T</td>\n",
       "      <td>6153.0</td>\n",
       "      <td>-7241.0</td>\n",
       "      <td>-1095.0</td>\n",
       "      <td>-2183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>20201019</td>\n",
       "      <td>TF</td>\n",
       "      <td>-1316.0</td>\n",
       "      <td>-1355.0</td>\n",
       "      <td>2257.0</td>\n",
       "      <td>-414.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>20201019</td>\n",
       "      <td>TS</td>\n",
       "      <td>-2576.0</td>\n",
       "      <td>757.0</td>\n",
       "      <td>-942.0</td>\n",
       "      <td>-2761.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>20201019</td>\n",
       "      <td>UR</td>\n",
       "      <td>-2241.0</td>\n",
       "      <td>3463.0</td>\n",
       "      <td>1317.0</td>\n",
       "      <td>2539.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>20201019</td>\n",
       "      <td>V</td>\n",
       "      <td>14961.0</td>\n",
       "      <td>7076.0</td>\n",
       "      <td>10025.0</td>\n",
       "      <td>32062.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>20201019</td>\n",
       "      <td>WH</td>\n",
       "      <td>-134.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.0</td>\n",
       "      <td>-119.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>20201019</td>\n",
       "      <td>ZC</td>\n",
       "      <td>2656.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8829.0</td>\n",
       "      <td>11485.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>20201019</td>\n",
       "      <td>ZN</td>\n",
       "      <td>13056.0</td>\n",
       "      <td>-2094.0</td>\n",
       "      <td>1183.0</td>\n",
       "      <td>12145.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date variety     永安期货     海通期货     中信期货        合计\n",
       "0   20201019       A  -5856.0   2150.0   3842.0     136.0\n",
       "1   20201019      AG  11654.0  17629.0  18061.0   47344.0\n",
       "2   20201019      AL   6131.0 -13768.0  -2093.0   -9730.0\n",
       "3   20201019      AP  -6673.0    501.0    824.0   -5348.0\n",
       "4   20201019      AU   5741.0   6397.0  11013.0   23151.0\n",
       "5   20201019      BU -44227.0   7908.0 -19416.0  -55735.0\n",
       "6   20201019       C  84530.0  21981.0 -84945.0   21566.0\n",
       "7   20201019      CF  -8518.0  22319.0  -4996.0    8805.0\n",
       "8   20201019      CJ   -946.0    544.0   -345.0    -747.0\n",
       "9   20201019      CS   4406.0   4032.0    798.0    9236.0\n",
       "10  20201019      CU  -1048.0   6550.0  24612.0   30114.0\n",
       "11  20201019      CY    113.0     60.0   -549.0    -376.0\n",
       "12  20201019      FG  15677.0 -13314.0  27076.0   29439.0\n",
       "13  20201019      FU -49990.0  -8929.0 -39074.0  -97993.0\n",
       "14  20201019      HC  24334.0  -9496.0    995.0   15833.0\n",
       "15  20201019       I  20035.0 -12118.0   -819.0    7098.0\n",
       "16  20201019      IC  -3655.0  -2062.0 -13039.0  -18756.0\n",
       "17  20201019      IF   1089.0   1928.0  -7731.0   -4714.0\n",
       "18  20201019      IH    917.0   1309.0  -5571.0   -3345.0\n",
       "19  20201019       J   3390.0   2440.0  -1929.0    3901.0\n",
       "20  20201019      JD    511.0   -997.0  -1258.0   -1744.0\n",
       "21  20201019      JR      NaN      5.0      NaN       5.0\n",
       "22  20201019      LR     -1.0      NaN      NaN      -1.0\n",
       "23  20201019      LU   3948.0  -8418.0  -4554.0   -9024.0\n",
       "24  20201019      MA -86053.0 -19818.0 -54694.0 -160565.0\n",
       "25  20201019      NI   4681.0   2594.0  -1709.0    5566.0\n",
       "26  20201019      NR   -553.0    -11.0   -885.0   -1449.0\n",
       "27  20201019      OI  11778.0   1690.0   4917.0   18385.0\n",
       "28  20201019       P  -5322.0 -10319.0  -4690.0  -20331.0\n",
       "29  20201019      PB  -1690.0  -1704.0   1702.0   -1692.0\n",
       "30  20201019      PF  26544.0  21555.0  -5999.0   42100.0\n",
       "31  20201019      PP  36860.0   9025.0  42997.0   88882.0\n",
       "32  20201019     PTA -55988.0      NaN -55285.0 -111273.0\n",
       "33  20201019      RB  -1534.0   7284.0  44758.0   50508.0\n",
       "34  20201019      RM  14735.0      NaN  11146.0   25881.0\n",
       "35  20201019      RR    255.0   -372.0   -621.0    -738.0\n",
       "36  20201019      RU   1575.0  -6495.0  -8145.0  -13065.0\n",
       "37  20201019      SA   -417.0  -8409.0   -357.0   -9183.0\n",
       "38  20201019      SF   1178.0   2773.0  -1406.0    2545.0\n",
       "39  20201019      SM  -1080.0   3075.0 -14182.0  -12187.0\n",
       "40  20201019      SN   -199.0    155.0    900.0     856.0\n",
       "41  20201019      SP -15316.0   5060.0  -9525.0  -19781.0\n",
       "42  20201019      SR  -1533.0  16134.0  14261.0   28862.0\n",
       "43  20201019       T   6153.0  -7241.0  -1095.0   -2183.0\n",
       "44  20201019      TF  -1316.0  -1355.0   2257.0    -414.0\n",
       "45  20201019      TS  -2576.0    757.0   -942.0   -2761.0\n",
       "46  20201019      UR  -2241.0   3463.0   1317.0    2539.0\n",
       "47  20201019       V  14961.0   7076.0  10025.0   32062.0\n",
       "48  20201019      WH   -134.0      NaN     15.0    -119.0\n",
       "49  20201019      ZC   2656.0      NaN   8829.0   11485.0\n",
       "50  20201019      ZN  13056.0  -2094.0   1183.0   12145.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# encoding: utf-8\n",
    "# import tensorflow as tf\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import akshare as ak\n",
    "\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "def get_trade_rank(market = 'SHF', date = None):\n",
    "    if date is None:\n",
    "        date = get_target_date(-1, \"%Y-%m-%d\")\n",
    "    if market == 'SHF':\n",
    "        return ak.get_shfe_rank_table(date)\n",
    "    if market == 'DCE':\n",
    "        return ak.get_dce_rank_table(date)\n",
    "    if market == 'CZC':\n",
    "        return ak.get_czce_rank_table(date)\n",
    "    if market == \"CFE\":\n",
    "        return ak.get_cffex_rank_table(date)\n",
    "    return None, '不支持的市场类型'\n",
    "def handle():\n",
    "    \"\"\"\n",
    "    handle data\n",
    "    - 去除逗号，\n",
    "    - 转化为浮点数类型\n",
    "    \"\"\"\n",
    "    markets = ['CZC', 'SHF', 'CFE', 'DCE']  # , 'CZC', 'SHF','CFE','DCE'\n",
    "    df = pd.DataFrame()\n",
    "    for market in markets:\n",
    "        begin = datetime.date(2020, 10, 19)\n",
    "        end = datetime.date(2020, 10, 19)\n",
    "        # begin = datetime.date.today()\n",
    "        # end = begin\n",
    "        for i in range((end - begin).days + 1):\n",
    "            day = begin + datetime.timedelta(days=i)\n",
    "            days = day.strftime('%Y%m%d')\n",
    "            try:\n",
    "                df1 = get_trade_rank(market, date=days)\n",
    "                for key, value in df1.items():\n",
    "                    value['date'] = days\n",
    "                    if market == 'CZC':\n",
    "                        value = value[value['symbol'] == value['variety']]\n",
    "                        value = value.applymap(lambda x: x.replace(',', '').replace(\"-\", \"\"))\n",
    "                        df = df.append(value)\n",
    "                    if market == 'SHF':\n",
    "                        value = value[-value['rank'].isin([999])]\n",
    "                        df = df.append(value)\n",
    "                    if market == 'DCE':\n",
    "                        value = value[-value['rank'].isin([999])]\n",
    "                        # value = value.applymap(lambda x: x.replace(',', '').replace(\"-\", \"\"))\n",
    "                        df = df.append(value)\n",
    "                    if market == 'CFE':\n",
    "                        value = value[-value['rank'].isin([999])]\n",
    "                        df = df.append(value)\n",
    "#                     print(df.tail())\n",
    "            except:\n",
    "                print(days, market, '数据异常')\n",
    "                continue\n",
    "    df = df.apply(pd.to_numeric, errors=\"ignore\")\n",
    "    #净持仓\n",
    "    long = df.groupby(['date', 'variety', 'long_party_name'])[\n",
    "        ['long_open_interest']].sum()\n",
    "    short = df.groupby(['date', 'variety', 'short_party_name'])[\n",
    "        ['short_open_interest']].sum()\n",
    "    # # 合并\n",
    "    frames = [long, short]\n",
    "    position = pd.concat(frames, axis=1, sort=True).fillna(0).reset_index()\n",
    "    # 字段更名\n",
    "    position = position.rename(columns={'level_0': 'date', 'level_1': 'variety', 'level_2': 'BrokerID'})\n",
    "    position['net'] = position.apply(lambda x: x['long_open_interest'] - x['short_open_interest'], axis=1)\n",
    "    party_names = ['永安期货', '海通期货', '中信期货']\n",
    "    df = pd.DataFrame()\n",
    "    for i in party_names:\n",
    "        try:\n",
    "            mem = position[position['BrokerID'] == i]\n",
    "            df1 = pd.DataFrame(mem)\n",
    "            df = df.append(df1)\n",
    "        except:\n",
    "            print('?')\n",
    "            continue\n",
    "    two_level_index_series = df.set_index(['date','variety', 'BrokerID'])['net']\n",
    "    net_df = two_level_index_series.unstack()\n",
    "    net_df['合计'] = net_df.apply(lambda x: x.sum(), axis=1)\n",
    "    net_df = net_df.rename_axis(columns=None).reset_index()\n",
    "    net_df = net_df[['date','variety', '永安期货', '海通期货', '中信期货', '合计']]\n",
    "    \n",
    "#     net_df.to_excel(path, index=False)\n",
    "    net_df\n",
    "    return net_df\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    path='d:/PyFile/np.xlsx'\n",
    "#     handle(path).net_df\n",
    "    display(handle())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
